package cn.edu.sdjzu.xg.kcsj.security;

import cn.edu.sdjzu.xg.kcsj.exception.BysjException;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class UserAssRoleDao {
    private static UserAssRoleDao userAssRoleDao = new UserAssRoleDao();
    private UserAssRoleDao() {}

    public static UserAssRoleDao getInstance() {
        return userAssRoleDao;
    }

    // 分页+条件
    public List<UserAssRole> findAll(List<Condition> conditionList, Pagination pagination, Connection connection) throws SQLException, BysjException {
        //创建集合类对象，用来保存所有记录代表的faculty对象
        List<UserAssRole> userAssRoles = new ArrayList<>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection, "user_ass_role");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM user_ass_role ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null && conditionList.size() > 0) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet.getInt("id");
            int user_id = resultSet.getInt("user_id");
            User userId = UserService.getInstance().find(user_id);
            int role_id = resultSet.getInt("role_id");
            Role roleId = RoleService.getInstance().find(role_id);
            userAssRoles.add(new UserAssRole(id, userId, roleId));
        }
        JdbcHelper.close(resultSet, pstmt);
        return userAssRoles;
    }

    // 按id查一条
    public UserAssRole find(int id,Connection connection) throws SQLException, BysjException {
        //创建sql语句
        String find_sql = "select * from user_ass_role where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        UserAssRole userAssRole = null;
        while (resultSet.next()) {
            int user_id = resultSet.getInt("user_id");
            User userId = UserService.getInstance().find(user_id);
            int role_id = resultSet.getInt("role_id");
            Role roleId = RoleService.getInstance().find(role_id);
            userAssRole = new UserAssRole(id, userId, roleId);
        }
        JdbcHelper.close(resultSet,pstmt);
        return userAssRole;
    }

    public Collection<Role> findRolesByUserId(int id,Connection connection) throws SQLException {
        Collection<Role> roles = new TreeSet<>();
        //创建sql语句
        String find_sql = "select role_id from user_ass_role where user_id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            int role_id = resultSet.getInt("role_id");
            Role role = RoleService.getInstance().find(role_id);
            roles.add(role);
        }
        JdbcHelper.close(resultSet,pstmt);
        return roles;
    }

    // 更新
    public boolean update(UserAssRole userAssRole,Connection connection) throws SQLException {
        //创建sql语句
        String updateUserAssRole_sql = "update user_ass_role set user_id=?,role_id=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateUserAssRole_sql);
        //为预编译参数赋值
        pstmt.setInt(3,userAssRole.getId());
        pstmt.setInt(1,userAssRole.getUser().getId());
        pstmt.setInt(2,userAssRole.getRole().getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }


    // 添加
    public boolean add(UserAssRole userAssRole,Connection connection) throws SQLException {
        //创建sql语句
        String addUserAssRole_sql =
                "insert into user_ass_role (user_id, role_id) values " +
                        "(?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addUserAssRole_sql);
        //为预编译参数赋值
        pstmt.setInt(1,userAssRole.getUser().getId());
        pstmt.setInt(2,userAssRole.getRole().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            return true;
        } else {
            return false;
        }
    }

    // 删除
    public boolean delete(int id,Connection connection) throws SQLException{
        //创建sql语句
        String deleteUserAssRole_sql = "DELETE FROM user_ass_role WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteUserAssRole_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            return true;
        }else {
            return false;
        }
    }
}
